package com.itheima.jdbc.数据库MYSQL.封装对象JDBC;


import com.itheima.jdbc.数据库MYSQL.util.JDBCUtil;
import com.itheima.jdbc.数据库MYSQL.封装对象JDBC.domain.Emp;

import java.sql.*;
import java.util.ArrayList;

/**/
public class JDBC集合封装emp表 {
    public static void main(String[] args) {
        ArrayList<Emp> all = findAll2();
        //System.out.println(all.size());
        for (Emp emp : all) {
            System.out.println(emp);
        }

    }

    public static ArrayList<Emp> findAll() {
        Connection con = null;
        Statement stem = null;
        ResultSet rs = null;
        ArrayList<Emp> list = null;
        try {
            //1.注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            //2.获取数据库连接
            con = DriverManager.getConnection("jdbc:mysql:///db3", "root", "nddwm");
            //3.定义SQL
            String sql = "select * from emp";
            //4.获取执行SQL的对象
            stem = con.createStatement();
            //5.执行SQL
            rs = stem.executeQuery(sql);
            //创建集合收集数据
            list = new ArrayList<Emp>();
            Emp emp = null;
            while (rs.next()) {
                //.获取数据
                int id = rs.getInt("id");
                String ename = rs.getString("ename");
                int job_id = rs.getInt("job_id");
                int mgr = rs.getInt("mgr");
                //SQL下的Date是util下的Date子类(多态,对象上转型)
                Date joindate = rs.getDate("joindate");
                double salary = rs.getDouble("salary");
                double bonus = rs.getDouble("bonus");
                int dept_id = rs.getInt("dept_id");
                emp = new Emp();
                emp.setId(id);
                emp.setEname(ename);
                emp.setJob_id(job_id);
                emp.setMgr(mgr);
                emp.setJoindate(joindate);
                emp.setSalary(salary);
                emp.setBonus(bonus);
                emp.setDept_id(dept_id);
                //装载集合
                list.add(emp);
            }
            if (rs != null) {
                rs.close();
            }
            if (con != null) {
                con.close();
            }
            if (stem != null) {
                stem.close();
            }

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    public static ArrayList<Emp> findAll2() {
        Connection con = null;
        Statement stem = null;
        ResultSet rs = null;
        ArrayList<Emp> list = null;
        try {
            con= JDBCUtil.getConnection();
            //3.定义SQL
            String sql = "select * from emp";
            //4.获取执行SQL的对象
            stem = con.createStatement();
            //5.执行SQL
            rs = stem.executeQuery(sql);
            //创建集合收集数据
            list = new ArrayList<Emp>();
            Emp emp = null;
            while (rs.next()) {
                //.获取数据
                int id = rs.getInt("id");
                String ename = rs.getString("ename");
                int job_id = rs.getInt("job_id");
                int mgr = rs.getInt("mgr");
                //SQL下的Date是util下的Date子类(多态,对象上转型)
                Date joindate = rs.getDate("joindate");
                double salary = rs.getDouble("salary");
                double bonus = rs.getDouble("bonus");
                int dept_id = rs.getInt("dept_id");
                emp = new Emp();
                emp.setId(id);
                emp.setEname(ename);
                emp.setJob_id(job_id);
                emp.setMgr(mgr);
                emp.setJoindate(joindate);
                emp.setSalary(salary);
                emp.setBonus(bonus);
                emp.setDept_id(dept_id);
                //装载集合
                list.add(emp);
            }
            JDBCUtil.colse(rs,stem,con);
        }catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

}
